package com.ce.pms.charge.service.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.ce.pms.base.service.impl.PmsBaseServiceImpl;
import com.ce.pms.charge.dao.IMeterDataRegisterDao;
import com.ce.pms.charge.dao.IPeriodDao;
import com.ce.pms.charge.model.PeriodDomain;
import com.ce.pms.charge.service.IExcelExportService;
import com.ce.pms.charge.service.IExcelImportService;
import com.ce.pms.charge.service.IMeterDataRegisterService;
import com.ce.pms.charge.vo.ImportErrorOrWarnVO;
import com.ce.pms.common.model.SysrefcodeDO;
import com.ce.pms.customer.dao.IClientRHouseDao;
import com.ce.pms.customer.dao.IProRightDao;
import com.ce.pms.customer.model.ProRightDO;
import com.ce.pms.exception.BusinessException;
import com.ce.pms.house.dao.IFlatDao;
import com.ce.pms.house.dao.IMeterDao;
import com.ce.pms.house.dao.IProjectDao;
import com.ce.pms.house.model.MeterDomain;
import com.ce.pms.house.model.ProjectDomain;
import com.ce.util.BlankUtil;
import com.ce.util.DateUtil;

public class ExcelImportServiceImpl extends PmsBaseServiceImpl implements
		IExcelImportService {

	//private static ProjectDomain projectDomain;
	private ThreadLocal<ProjectDomain> projectDomain=new ThreadLocal<ProjectDomain>();

	//private static Map meterMaxMap;
	private ThreadLocal<Map> meterMaxMap=new ThreadLocal<Map>();

	//private static Map proRightMap;

	private ThreadLocal<Map> proRightMap=new ThreadLocal<Map>();

	//private static PeriodDomain periodDomain;

	private ThreadLocal<Map> waterMeterMap=new ThreadLocal<Map>();

	private ThreadLocal<PeriodDomain> periodDomain=new ThreadLocal<PeriodDomain>();

	private ThreadLocal<Map> ammerMeterMap=new ThreadLocal<Map>();

	//private static Map waterMeterMap;

	//private static Map ammerMeterMap;
	/**
	 * 方法描述: 验证excel文件,
	 *
	 * @author 胡煦 Create on:2008-9-1
	 * @param workbook
	 *            <参数类型，参数集合>
	 * @return Map Edit author: Edit on: 修改原因：
	 */
	public Map checkTheExcel(HSSFWorkbook workbook) {

		this.checkExcelFileHead(workbook);
		List<ImportErrorOrWarnVO> errorVOList = new ArrayList<ImportErrorOrWarnVO>();
		List<ImportErrorOrWarnVO> warnVOList = new ArrayList<ImportErrorOrWarnVO>();

		Map meterMap = new HashMap();
		meterMap.put("水表", meterDao.getMAXZByMeterTypeCode("001"));
		meterMap.put("电表", meterDao.getMAXZByMeterTypeCode("002"));
		meterMaxMap.set(meterMap);
		Date recordTime = meterDataRegisterDao.getMaxRecordTime();
		Map<String, List> map = new HashMap<String, List>();
		Map composeDataMap = this.getComposeData(workbook);
		if (!BlankUtil.isBlank(composeDataMap.get("errorVOList"))) {
			errorVOList.addAll((List) composeDataMap.get("errorVOList"));
		}
		for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++)

		{

			if (null != workbook.getSheetAt(numSheets))

			{
				// 获得一个sheet
				HSSFSheet aSheet = workbook.getSheetAt(numSheets);
				// 获取Sheet的名称
				String sheetName = workbook.getSheetName(numSheets).trim();
				// 验证Sheet名称的合法性
				ImportErrorOrWarnVO errorOrWarnVO = this
						.checkExcelFileSheetName(sheetName);
				if (!BlankUtil.isBlank(errorOrWarnVO)) {
					errorVOList.add(errorOrWarnVO);
				}

				// 定义存放导入数据字符串行的集合，用于判断是否有重复记录
				List<String> list = new ArrayList<String>();
				for (int rowNumOfSheet = 1; rowNumOfSheet <= aSheet
						.getLastRowNum(); rowNumOfSheet++)

				{
					// 获取Sheet中的某一行
					// HSSFRow aRow = aSheet.getRow(rowNumOfSheet);
					// 获取错误参数集合
					errorVOList
							.addAll(this.checkTheErrorData(composeDataMap,
									aSheet, rowNumOfSheet, sheetName, list,
									recordTime));
					// 获取警告参数集合
					warnVOList.addAll(this.checkTheWarnData(aSheet, sheetName,
							rowNumOfSheet));
				}
			}

		}
		map.put("error", errorVOList);
		map.put("warn", warnVOList);
		return map;
	}

	/**
	 * 方法描述: 批量读取保存excel中记录
	 *
	 * @author 胡煦 Create on:2008-8-21
	 * @param HSSFWorkbook
	 *            workbook,Long creatorId
	 * @return boolean Edit author: Edit on: 修改原因：
	 */
	public boolean readExcelAndSave(HSSFWorkbook workbook, Long creatorId) {

		List<Object[]> list = this.getExcelList(workbook, creatorId);
		return meterDataRegisterDao.batchInsertChargeDatas(list);
	}

	/**
	 * 方法描述: 创建工作簿,用来和导入的EXCEL进行比对校验
	 *
	 * @author 胡煦 Create on:2008-8-26
	 * @param Long
	 *            buildingId
	 * @return HSSFWorkbook Edit author: Edit on: 修改原因：
	 */
	private HSSFWorkbook createHSSFWorkbook(Long buildingId) {
		return excelExportService.createHSSFWorkbook(buildingId);
	}

	/**
	 * 方法描述: 验证整个EXCEL的文件头
	 *
	 * @author 胡煦 Create on:2008-10-16
	 * @param nameStr：查询条件班级名称
	 * @return 返回当前页的查询结果 Edit author: Edit on: 修改原因：
	 */
	private void checkExcelFileHead(HSSFWorkbook workbook) {
		for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) {
			if(workbook.getSheetAt(numSheets).getLastRowNum() > 0){
				this.checkSheetFileHead(workbook.getSheetAt(numSheets));
			}
		}
	}

	/**
	 * 方法描述: 获取从数据库获得的组合数据
	 *
	 * @author 胡煦 Create on:2008-8-26
	 * @param Long
	 *            buildingId
	 * @return HSSFWorkbook Edit author: Edit on: 修改原因：
	 */
	private Map getComposeData(HSSFWorkbook workbook) {

		Long buildingId = null;
		List<ImportErrorOrWarnVO> errorVOList = new ArrayList<ImportErrorOrWarnVO>();
		Map map = new HashMap();
		HSSFSheet sheet = workbook.getSheetAt(0);
		HSSFRow row = sheet.getRow(1);
		if(row.getCell((short) 0).getCellType()!=HSSFCell.CELL_TYPE_BLANK&&!BlankUtil.isBlank(row)&&
				row.getCell((short) 3).getCellType()!=HSSFCell.CELL_TYPE_BLANK){
			//根据建设期名称得到建设期实体
			ProjectDomain projectDo = projectDao.getProjectByName(row.getCell((short) 0)
					.getStringCellValue().trim());
			projectDomain.set(projectDo);
			Object[] flatObjs = getDomainByCell(row);
			if (BlankUtil.isBlank(flatObjs)
					|| BlankUtil.isBlank(projectDomain)) {
				ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
				errorVO.setRowNumber(2);
				errorVO.setCopyDataType(workbook
						.getSheetName(0));
				errorVO.setParticularInfo("期数、楼栋的值不正确");
				errorVOList.add(errorVO);
			} else {
				if (BlankUtil.isBlank(buildingId)) {
					buildingId = new Long(flatObjs[1].toString());
				}
			}
		if (!BlankUtil.isBlank(errorVOList)) {
			map.put("errorVOList", errorVOList);
			return map;
		}

		if (!BlankUtil.isBlank(buildingId)) {
			//根据楼栋id查找其下所有房间的有效产权
			Map proRightTempMap = proRightDao.getEffectiveProRightsByBuildingId(buildingId);
			proRightMap.set(proRightTempMap);

			// 根据楼栋ID得到当前收费管理区有效的收费周期
			PeriodDomain periodDo = periodDao.findAvailPeriodBy(
					buildingId);
			periodDomain.set(periodDo);
			//通过楼栋id和走表类型code查询走表实体List
			Map waterMeterTempMap = meterDao.getMeterByBuildingId(buildingId, SysrefcodeDO.HM_WATER_METER_TYPE_CODE);
			Map ammerMeterTempMap = meterDao.getMeterByBuildingId(buildingId, SysrefcodeDO.HM_AMMETER_METER_TYPE_CODE);
			waterMeterMap.set(waterMeterTempMap);
			ammerMeterMap.set(ammerMeterTempMap);
			HSSFWorkbook workbook2 = createHSSFWorkbook(buildingId);
			List<String> list = new ArrayList();
			for (int numSheets = 0; numSheets < workbook2.getNumberOfSheets(); numSheets++)

			{

				if (null != workbook2.getSheetAt(numSheets))

				{
					// 获取Sheet的名称
					String sheetName = workbook2.getSheetName(numSheets).trim();
					// 获得一个sheet
					HSSFSheet aSheet = workbook2.getSheetAt(numSheets);
					for (int rowNumOfSheet = 1; rowNumOfSheet <= aSheet
							.getLastRowNum(); rowNumOfSheet++)

					{
						// 获取Sheet中的某一行
						HSSFRow aRow = aSheet.getRow(rowNumOfSheet);

						// 定义导入数据字符串行，用于以期数、房号为条件，查询其数据库中的楼宇，楼层和底数是否与文件中的一致，校验其是否人工修改其相关的字段值
						String str = aRow.getCell((short) 0)
								.getStringCellValue().trim()
								+ aRow.getCell((short) 1).getNumericCellValue()
								+ aRow.getCell((short) 2).getNumericCellValue()
								+ aRow.getCell((short) 3).getStringCellValue()
										.trim()
								+ aRow.getCell((short) 4).getNumericCellValue();
						// 放入集合
						list.add(str);
					}
					map.put(sheetName, list);
				}
			}

		}
		}
		return map;
	}

	/**
	 * 方法描述: 判断期数、楼栋、层数,房间号码和走表底数的组合是否正确
	 *
	 * @author 胡煦 Create on:2008-9-1
	 * @param
	 * @return List<ImportErrorOrWarnVO> Edit author: Edit on: 修改原因：
	 */
	private ImportErrorOrWarnVO checkComposeData(Map map, HSSFRow aRow,
			String sheetName, int rowNumOfSheet) {

		// 定义导入数据字符串行，用于判断是否有重复记录
		String str = aRow.getCell((short) 0).getStringCellValue().trim()
				+ aRow.getCell((short) 1).getNumericCellValue()
				+ aRow.getCell((short) 2).getNumericCellValue()
				+ aRow.getCell((short) 3).getStringCellValue().trim()
				+ aRow.getCell((short) 4).getNumericCellValue();

		List<String> list = (List<String>) map.get(sheetName);
		// 判断EXCEL中是否有重复记录
		if (BlankUtil.isBlank(compareInList(str, list))) {
			ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
			errorVO.setRowNumber(rowNumOfSheet + 1);
			errorVO.setCopyDataType(sheetName);
			errorVO.setParticularInfo("期数、楼栋、层数,房间号码和上次读数的组合不正确");
			return errorVO;
		}
		return null;
	}

	/**
	 * 方法描述: 验证集合中是否存在某值
	 *
	 * @author 胡煦 Create on:2008-9-10
	 * @param String
	 *            str,List<String> list
	 * @return String Edit author: Edit on: 修改原因：
	 */
	private String compareInList(String str, List<String> list) {

		for (String string : list) {
			if (string.equals(str)) {
				return string;
			}
		}
		return null;
	}

	/**
	 * 方法描述: 验证错误数据
	 *
	 * @author 胡煦 Create on:2008-9-1
	 * @param nameStr：查询条件班级名称
	 * @return 返回当前页的查询结果 Edit author: Edit on: 修改原因：
	 */
	private List<ImportErrorOrWarnVO> checkTheErrorData(Map map,
			HSSFSheet aSheet, int rowNumOfSheet, String sheetName,
			List<String> list, Date recordTime) {
		List<ImportErrorOrWarnVO> errorVOList = new ArrayList<ImportErrorOrWarnVO>();
		// 获取Sheet中的某一行
		HSSFRow aRow = aSheet.getRow(rowNumOfSheet);
		if(!BlankUtil.isBlank(aRow)){
			// 验证EXCEL文件插入字段是否合法
			errorVOList.addAll(checkExcelFieldIsTrue(aRow, rowNumOfSheet,
					sheetName, recordTime));
			// 验证单元格是否为空
			errorVOList
					.addAll(checkAllExcelCellNull(aRow, rowNumOfSheet, sheetName));
			if (BlankUtil.isBlank(map.get("errorVOList"))&&BlankUtil.isBlank(errorVOList)) {
				// 查询其数据库中的楼宇，楼层和底数是否与文件中的一致，校验其是否人工修改其相关的字段值
				if (!BlankUtil.isBlank(checkComposeData(map, aRow, sheetName,
						rowNumOfSheet))) {
					errorVOList.add(checkComposeData(map, aRow, sheetName,
							rowNumOfSheet));
				}
				// 判断EXCEL中是否有重复记录
				errorVOList
						.addAll(checkRepeatData(list, aRow, sheetName, rowNumOfSheet));
			}
		}
		return errorVOList;
	}

	/**
	 * 方法描述: 验证警告数据
	 *
	 * @author 胡煦 Create on:2008-9-1
	 * @param workbook
	 * @return 返回当前页的查询结果 Edit author: Edit on: 修改原因：
	 */
	private List<ImportErrorOrWarnVO> checkTheWarnData(HSSFSheet aSheet,
			String sheetName, int rowNumOfSheet) {
		Double lastReading = new Double(0);
		Double thisReading = new Double(0);
		List<ImportErrorOrWarnVO> errorVOList = new ArrayList<ImportErrorOrWarnVO>();
		// 获取Sheet中的某一行
		HSSFRow row = aSheet.getRow(rowNumOfSheet);
		if(!BlankUtil.isBlank(row)){
			// 验证当前工作区间、当前行的本月读数是否大于底数
			if ((!BlankUtil.isBlank((row.getCell((short) 4)))&& row.getCell((short) 4).getCellType() != HSSFCell.CELL_TYPE_BLANK)
					&& (!BlankUtil.isBlank((row.getCell((short) 5)))&& row.getCell((short) 5).getCellType() != HSSFCell.CELL_TYPE_BLANK)) {
				if ((row.getCell((short) 4).getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
						&& (row.getCell((short) 5).getCellType() == HSSFCell.CELL_TYPE_NUMERIC)) {
					lastReading = row.getCell((short) 4).getNumericCellValue();
					thisReading = row.getCell((short) 5).getNumericCellValue();
					if (thisReading.compareTo(lastReading) < 0) {
						ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
						errorVO.setRowNumber(rowNumOfSheet + 1);
						errorVO.setCopyDataType(sheetName);
						errorVO.setParticularInfo("本次读数小于上次读数");
						errorVOList.add(errorVO);
					}
				}
			}
			if (!BlankUtil.isBlank(row.getCell((short) 1))&& row.getCell((short) 1).getCellType() != HSSFCell.CELL_TYPE_BLANK
					&& !BlankUtil.isBlank(row.getCell((short) 3))&& row.getCell((short) 3).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
				// 验证走表数据在当前收费周期是否有重复
				if (meterDataRegisterDao.getMeterDatasListByNames(
						(int) row.getCell((short) 1).getNumericCellValue(),
						row.getCell((short) 3).getStringCellValue().trim(),
						sheetName).size() > 0) {
					ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
					errorVO.setRowNumber(rowNumOfSheet + 1);
					errorVO.setCopyDataType(sheetName);
					errorVO.setParticularInfo("该走表数据已经存在");
					errorVOList.add(errorVO);
				}
			}

		}
		return errorVOList;
	}

	/**
	 * 方法描述: 读取保存excel中记录
	 *
	 * @author 胡煦 Create on:2008-8-21
	 * @param sourcefile
	 * @return boolean Edit author: Edit on: 修改原因：
	 */
	private List<Object[]> getExcelList(HSSFWorkbook workbook, Long creatorId) {

		List<Object[]> list = new ArrayList<Object[]>();
		for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++)

		{

			if (0 != workbook.getSheetAt(numSheets).getLastRowNum())

			{
				// 获得一个sheet
				HSSFSheet aSheet = workbook.getSheetAt(numSheets);
				// 获取Sheet的名称
				String sheetName = workbook.getSheetName(numSheets).trim();

				for (int rowNumOfSheet = 1; rowNumOfSheet <= aSheet
						.getLastRowNum(); rowNumOfSheet++)

				{

					if (null != aSheet.getRow(rowNumOfSheet))

					{
						// 获取Sheet中的某一行
						HSSFRow aRow = aSheet.getRow(rowNumOfSheet);
						Object[] flatObjs = getDomainByCell(aRow);
						if(!BlankUtil.isBlank(flatObjs)){
							// 写入房间实体
							Long houseId = new Long(flatObjs[0].toString());
							ProRightDO proRightDO = (ProRightDO) proRightMap.get().get(houseId);
							// 获取集合
							list.add(this.setExcelRowToDomain(aRow,houseId,proRightDO,sheetName,creatorId));


						}

					}

				}

			}

		}
		return list;
	}

	/**
	 * 方法描述: 判断期数、楼栋、层数和房间号码的组合是否重复
	 *
	 * @author 胡煦 Create on:2008-9-1
	 * @param nameStr：查询条件班级名称
	 * @return 返回当前页的查询结果 Edit author: Edit on: 修改原因：
	 */
	private List<ImportErrorOrWarnVO> checkRepeatData(List<String> list,
			HSSFRow aRow, String sheetName, int rowNumOfSheet) {

		List<ImportErrorOrWarnVO> errorVOList = new ArrayList<ImportErrorOrWarnVO>();

		if(BlankUtil.isBlank(aRow)){
			// 定义导入数据字符串行，用于判断是否有重复记录
			String str = aRow.getCell((short) 0).getStringCellValue().trim()
					+ aRow.getCell((short) 1).getNumericCellValue()
					+ aRow.getCell((short) 2).getNumericCellValue()
					+ aRow.getCell((short) 3).getStringCellValue().trim();
			// 放入集合
			list.add(str);
			Object[] objects = list.toArray();
			// 判断EXCEL中是否有重复记录
			if (!judgeStrRepeat(objects)) {
				ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
				errorVO.setRowNumber(rowNumOfSheet + 1);
				errorVO.setCopyDataType(sheetName);
				errorVO.setParticularInfo("数据重复导入");
				errorVOList.add(errorVO);
				list.clear();
			}
		}
		return errorVOList;
	}

	/**
	 * 方法描述: 将Excel中某Sheet中的一行记录写进走表原始数据实体保存
	 *
	 * @author 胡煦 Create on:2008-8-27
	 * @param HSSFRow
	 *            aRow,int rowNumOfSheet,String sheetName
	 * @return ChargeDatasDomain Edit author: Edit on: 修改原因：
	 */
	private Object[] setExcelRowToDomain(HSSFRow aRow,Long houseId,ProRightDO proRightDO,
			String sheetName, Long creatorId) {

		// 获取相关数据库实体

		// 写入房间实体
		String owerName = clientRHouseDao.getOwnerNameBy(houseId);

		Long meterId = null;
		// 设置走表实体，根据sheet的名称确定走表类型
		if (sheetName.equals("水表")) {
			meterId = ((MeterDomain) waterMeterMap.get().get(houseId)).getKeyword();
		}
		if (sheetName.equals("电表")) {
			meterId = ((MeterDomain) ammerMeterMap.get().get(houseId)).getKeyword();
		}

		// 将数据状态设置为新建
		String statuz = SysrefcodeDO.FEE_CHARGE_DATAS_STATUS_NEW;

		// 写入走表上次读数
		BigDecimal lastReading = new BigDecimal(aRow.getCell((short) 4)
				.getNumericCellValue());
		// 写入走表本次读数
		BigDecimal thisReading = new BigDecimal(aRow.getCell((short) 5)
				.getNumericCellValue());
		// 写入用量，根据countWastage方法计算所得
		BigDecimal wastage = meterDataRegisterService.countWastage(lastReading,
				thisReading, meterId);
		// 写入抄表时间
		Date recordTime = null;
		if(aRow.getCell((short) 6).getCellType() != HSSFCell.CELL_TYPE_NUMERIC){
			recordTime = DateUtil.convertStrToDate(aRow.getCell((short) 6)
					.toString(), "yyyy-MM-dd");
		}else{
			recordTime = HSSFDateUtil.getJavaDate(aRow.getCell((short) 6).getNumericCellValue());
		}
		// Date sysDate = this.getDate();

		Object[] objects = new Object[] { houseId, proRightDO.getProRightId(), owerName,
				periodDomain.get().getKeyword(), meterId, statuz, lastReading, thisReading, wastage,
				recordTime, creatorId };
		return objects;

	}

	/**
	 * 方法描述: 验证EXCEL单元格是否为空
	 *
	 * @author 胡煦 Create on:2008-8-20
	 * @param cell，row ,
	 *            column
	 * @return void Edit author: Edit on: 修改原因：
	 */
	private List<ImportErrorOrWarnVO> checkAllExcelCellNull(HSSFRow aRow,
			int rowNumOfSheet, String meterType) {
		List<ImportErrorOrWarnVO> errorVOList = new ArrayList<ImportErrorOrWarnVO>();
		for (short cellNumOfRow = 0; cellNumOfRow < 7; cellNumOfRow++)

		{
			HSSFCell aCell = aRow.getCell(cellNumOfRow);
			// 验证某个单元格是否为空
			if (BlankUtil.isBlank(aCell)
					|| aCell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
				ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
				errorVO.setRowNumber(rowNumOfSheet + 1);
				errorVO.setCopyDataType(meterType);
				short NumOfRow = (short) (cellNumOfRow + 1);
				errorVO.setParticularInfo("第" + NumOfRow + "列单元格为空");
				errorVOList.add(errorVO);
			}
		}
		return errorVOList;
	}

	// /**
	// * 方法描述: 验证EXCEL文件单元格
	// *
	// * @author 胡煦 Create on:2008-8-20
	// * @param sheet
	// * @return boolean Edit author: Edit on: 修改原因：
	// */
	// private void checkExcelCellLike(String read, String cell, int rouNum,
	// String meterType) {
	// ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
	// if (!read.equalsIgnoreCase(cell)) {
	// // throw new BusinessException(
	// // "charge:excel_cell_error_exception,"
	// // + column + ":" + cell,
	// // BusinessException.WARNING);
	// errorVO.setRowNumber(rouNum);
	// errorVO.setCopyDataType(meterType);
	// errorVO.setParticularInfo("房号为非法数据");
	// }
	// }

	/**
	 * 方法描述: 验证EXCEL表头文件单元格
	 *
	 * @author 胡煦 Create on:2008-8-20
	 * @param sheet
	 * @return boolean Edit author: Edit on: 修改原因：
	 */
	private void checkExcelHeadCellLike(HSSFCell cell, String cellName, int column) {

		if (BlankUtil.isBlank(cell)||!cellName.equalsIgnoreCase(cell.getStringCellValue().trim())) {
			throw new BusinessException("charge:excel_cell_error_exception,"
					+ column + ":" + cellName, BusinessException.WARNING);
		}
	}

	/**
	 * 方法描述: 验证EXCEL文件表头格式
	 *
	 * @author 胡煦 Create on:2008-8-20
	 * @param sheet
	 * @return boolean Edit author: Edit on: 修改原因：
	 */
	private boolean checkSheetFileHead(HSSFSheet sheet) {

		HSSFRow row = null;

		HSSFCell cell = null;

		row = sheet.getRow(0);
		// 验证第一列为建设期且不为空
		cell = row.getCell((short) 0);

		this.checkExcelHeadCellLike(cell, "建设期", 1);
		// 验证第二列为楼栋且不为空
		cell = row.getCell((short) 1);

		this.checkExcelHeadCellLike(cell, "楼栋", 2);
		// 验证第三列为楼层且不为空
		cell = row.getCell((short) 2);


		this.checkExcelHeadCellLike(cell, "楼层", 3);
		// 验证第四列为房号且不为空
		cell = row.getCell((short) 3);


		this.checkExcelHeadCellLike(cell, "房号", 4);
		// 验证第五列为电表底数且不为空
		cell = row.getCell((short) 4);


		this.checkExcelHeadCellLike(cell, "上次读数", 5);
		// 验证第六列为本月读数且不为空
		cell = row.getCell((short) 5);


		this.checkExcelHeadCellLike(cell, "本次读数", 6);
		// 验证第七列为抄表时间且不为空
		cell = row.getCell((short) 6);


		this.checkExcelHeadCellLike(cell, "抄表时间", 7);

		return true;
	}

	/**
	 * 方法描述: 判断数组中是否有重复值
	 *
	 * @author 胡煦 Create on:2008-8-22
	 * @param String []
	 * @return boolean Edit author: Edit on: 修改原因：
	 */
	private boolean judgeStrRepeat(Object[] str) {
		Set set = new HashSet();

		for (int i = 0, n = str.length; i < n; i++)

		{
			if (!BlankUtil.isBlank(str[i])) {
				if (set.contains(str[i]))

				{
					return false;

				}
			}
			set.add(str[i]);

		}
		return true;
	}

	/**
	 * 方法描述: 根据单元格的值查询相应实体
	 *
	 * @author 胡煦 Create on:2008-9-3
	 * @param HSSFRow
	 * @return Map Edit author: Edit on: 修改原因：
	 */
	private Object[] getDomainByCell(HSSFRow aRow) {

		if (BlankUtil.isBlank(aRow)||BlankUtil.isBlank(projectDomain)
				|| BlankUtil.isBlank(aRow.getCell((short) 3))) {
			return null;
		}
		// 根据建设期数ID和房号得到flatDomain
		if(HSSFCell.CELL_TYPE_STRING == aRow.getCell((short) 3).getCellType()){
			Object[] objects =  flatDao
			.getFlatObjsByProjectAndNumber(projectDomain.get().getKeyword(), aRow
					.getCell((short) 3).getStringCellValue().trim());
			return objects;
		}
		return null;
	}

	/**
	 * 方法描述: 验证EXCEL文件插入字段是否合法
	 *
	 * @author 胡煦 Create on:2008-8-20
	 * @param sheet
	 * @return boolean Edit author: Edit on: 修改原因：
	 */
	private List<ImportErrorOrWarnVO> checkExcelFieldIsTrue(HSSFRow row,
			int rowNumOfSheet, String meterType, Date recordTime) {
		List<ImportErrorOrWarnVO> errorVOList = new ArrayList<ImportErrorOrWarnVO>();
		// List<ErrorVO> errorVOList = new ArrayList<ErrorVO>();
		if (!BlankUtil.isBlank(row.getCell((short) 1))
				&& row.getCell((short) 1).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
			// 验证是楼栋是否数字型
			if (!(row.getCell((short) 1).getCellType() == HSSFCell.CELL_TYPE_NUMERIC)) {
				// throw new
				// BusinessException("charge:excel_building_floor_type_exception,"+rowNumOfSheet+"楼宇"+"数字",
				// BusinessException.WARNING);
				ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
				errorVO.setRowNumber(rowNumOfSheet + 1);
				errorVO.setCopyDataType(meterType);
				errorVO.setParticularInfo("楼栋必须为数字");
				errorVOList.add(errorVO);
			}
		}
		if (!BlankUtil.isBlank(row.getCell((short) 2))
				&& row.getCell((short) 2).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
			// 验证是层数是否数字型
			if (!(row.getCell((short) 2).getCellType() == HSSFCell.CELL_TYPE_NUMERIC)) {
				// throw new
				// BusinessException("charge:excel_building_floor_type_exception,"+rowNumOfSheet+"楼层"+"数字",
				// BusinessException.WARNING);
				ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
				errorVO.setRowNumber(rowNumOfSheet + 1);
				errorVO.setCopyDataType(meterType);
				errorVO.setParticularInfo("层数必须为数字");
				errorVOList.add(errorVO);
			}
		}

		if (!BlankUtil.isBlank(row.getCell((short) 4))
				&& row.getCell((short) 4).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
			// 验证是底数是否数字型
			if (!(row.getCell((short) 4).getCellType() == HSSFCell.CELL_TYPE_NUMERIC)) {
				ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
				errorVO.setRowNumber(rowNumOfSheet + 1);
				errorVO.setCopyDataType(meterType);
				errorVO.setParticularInfo("上次读数必须为数字");
				errorVOList.add(errorVO);
			}
		}

		if (!BlankUtil.isBlank(row.getCell((short) 5))
				&& row.getCell((short) 5).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
			// 验证本次读数是否是数字型
			Pattern pattern = Pattern.compile("^\\d+(\\.\\d+)?$");
			Matcher matcher = pattern.matcher(row.getCell((short) 5).toString());
			boolean a = matcher.matches();
			if (!a) {
				ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
				errorVO.setRowNumber(rowNumOfSheet + 1);
				errorVO.setCopyDataType(meterType);
				errorVO.setParticularInfo("本次读数必须为数字且不为空");
				errorVOList.add(errorVO);
			} else {
				Double myDouble = new Double(row.getCell((short) 5).toString());
				BigDecimal maxNumber = new BigDecimal(meterMaxMap.get()
						.get(meterType).toString());
				// 验证本次读数不超过最大读数
				if (myDouble.compareTo(maxNumber.doubleValue()) == 1) {
					ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
					errorVO.setRowNumber(rowNumOfSheet + 1);
					errorVO.setCopyDataType(meterType);
					errorVO.setParticularInfo("本次读数不能超过" + maxNumber + "");
					errorVOList.add(errorVO);
				}else{
					// 验证本次读数的小数位不超过2位
					String tempString = myDouble.toString();
					int startInt = tempString.indexOf(".");
					int tempint = tempString.substring(startInt + 1).length();// 取小数点后共有几位
					if (tempint > 2) {
						ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
						errorVO.setRowNumber(rowNumOfSheet + 1);
						errorVO.setCopyDataType(meterType);
						errorVO.setParticularInfo("本次读数的小数位不能超过2位");
						errorVOList.add(errorVO);
					}
				}
			}
		}

		// 验证本次抄表时间格式是否正确
		if (!BlankUtil.isBlank(row.getCell((short) 6))
				&& row.getCell((short) 6).getCellType() != HSSFCell.CELL_TYPE_BLANK) {

			Pattern pattern = Pattern.compile("^((\\d{2}(([02468][048])|([13579][26]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])))))|(\\d{2}(([02468][1235679])|([13579][01345789]))[\\-\\/\\s]?((((0?[13578])|(1[02]))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\\-\\/\\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\\-\\/\\s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))");
			Matcher matcher = null;
			if(row.getCell((short) 6).getCellType() != HSSFCell.CELL_TYPE_NUMERIC){
				matcher = pattern.matcher(row.getCell((short) 6).toString());
			}else{
				String dateStr = DateUtil.convertDateToStr(row.getCell((short) 6).getDateCellValue(), "yyyy-MM-dd");
				matcher = pattern.matcher(dateStr);
			}

			boolean a = matcher.matches();
			if (!a) {
				ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
				errorVO.setRowNumber(rowNumOfSheet + 1);
				errorVO.setCopyDataType(meterType);
				errorVO.setParticularInfo("本次抄表时间格式不正确");
				errorVOList.add(errorVO);
			} else {
				Date date = null;
				if(row.getCell((short) 6).getCellType() != HSSFCell.CELL_TYPE_NUMERIC){
					date = DateUtil.convertStrToDate(row.getCell((short) 6)
							.toString(), "yyyy-MM-dd");
				}else{
					date = HSSFDateUtil.getJavaDate(row.getCell((short) 6).getNumericCellValue());
				}
				// 验证本次抄表时间必须大于等于数据库中最大的抄表时间
				if (!BlankUtil.isBlank(recordTime)) {
					if (recordTime.compareTo(date) == 1) {
						ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
						errorVO.setRowNumber(rowNumOfSheet + 1);
						errorVO.setCopyDataType(meterType);
						errorVO.setParticularInfo("本次抄表时间为非法数据");
						errorVOList.add(errorVO);
					}
				}
			}
		}
		return errorVOList;
	}

	/**
	 * 方法描述: 验证EXCEL文件Sheet名
	 *
	 * @author 胡煦 Create on:2008-8-20
	 * @param sheet
	 * @return boolean Edit author: Edit on: 修改原因：
	 */
	private ImportErrorOrWarnVO checkExcelFileSheetName(String sheetName) {
		// 验证sheet的名字必须为电表或水表
		ImportErrorOrWarnVO errorVO = new ImportErrorOrWarnVO();
		if (!sheetName.equalsIgnoreCase("电表")
				&& !sheetName.equalsIgnoreCase("水表")) {
			errorVO.setRowNumber(1);
			errorVO.setCopyDataType(sheetName);
			errorVO.setParticularInfo("sheet的名字为非法数据");
			return errorVO;
		}
		return null;
	}

	private IMeterDataRegisterDao meterDataRegisterDao;

	private IFlatDao flatDao;

	private IProjectDao projectDao;

	private IMeterDao meterDao;

	private IPeriodDao periodDao;

	private IProRightDao proRightDao;

	private IMeterDataRegisterService meterDataRegisterService;

	private IClientRHouseDao clientRHouseDao;

	private IExcelExportService excelExportService;

	/**
	 * @param excelExportService
	 *            the excelExportService to set
	 */
	public void setExcelExportService(IExcelExportService excelExportService) {
		this.excelExportService = excelExportService;
	}

	/**
	 * @param clientRHouseDao
	 *            the clientRHouseDao to set
	 */
	public void setClientRHouseDao(IClientRHouseDao clientRHouseDao) {
		this.clientRHouseDao = clientRHouseDao;
	}

	/**
	 * @param periodDao
	 *            the periodDao to set
	 */
	public void setPeriodDao(IPeriodDao periodDao) {
		this.periodDao = periodDao;
	}

	/**
	 * @param meterDao
	 *            the meterDao to set
	 */
	public void setMeterDao(IMeterDao meterDao) {
		this.meterDao = meterDao;
	}

	/**
	 * @param flatDao
	 *            the flatDao to set
	 */
	public void setFlatDao(IFlatDao flatDao) {
		this.flatDao = flatDao;
	}

	/**
	 * @param meterDataRegisterDao
	 *            the meterDataRegisterDao to set
	 */
	public void setMeterDataRegisterDao(
			IMeterDataRegisterDao meterDataRegisterDao) {
		this.meterDataRegisterDao = meterDataRegisterDao;
	}

	/**
	 * @param projectDao
	 *            the projectDao to set
	 */
	public void setProjectDao(IProjectDao projectDao) {
		this.projectDao = projectDao;
	}

	/**
	 * @param proRightDao
	 *            the proRightDao to set
	 */
	public void setProRightDao(IProRightDao proRightDao) {
		this.proRightDao = proRightDao;
	}

	/**
	 * @param meterDataRegisterService
	 *            the meterDataRegisterService to set
	 */
	public void setMeterDataRegisterService(
			IMeterDataRegisterService meterDataRegisterService) {
		this.meterDataRegisterService = meterDataRegisterService;
	}

}
